#include <iostream>
#include <unistd.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <arpa/inet.h>
#include <sqlite3.h>
#include <signal.h>
#include <jsoncpp/json/json.h>

using namespace std;

// 消息类型【请求类型和响应类型】
/*
root["cmd"] = "timekeeperRegister_req";    root["cmd"] = "timekeeperRegister_OK";    //考勤员注册（timekeeper表 - 插入记录）
root["cmd"] = "timekeeperLogin_req";       root["cmd"] = "timekeeperLogin_OK";       //考勤员登录（timekeeper表 - 查找记录）
root["cmd"] = "punchmachineValidate_req";  root["cmd"] = "punchmachineValidate_OK";  //打卡机认证（punchmachine表 - 查找记录）
root["cmd"] = "insertEmployee_req";        root["cmd"] = "insertEmployee_OK";        //（employee表 - 插入记录）
root["cmd"] = "selectEmployee_req";        root["cmd"] = "selectEmployee_OK";        //（employee表 - 查找记录）
root["cmd"] = "updateEmployee_req";        root["cmd"] = "updateEmployee_OK";        //（employee表 - 更新记录）
root["cmd"] = "deleteEmployee_req";        root["cmd"] = "deleteEmployee_OK";        //（punch表 - 查找记录）
root["cmd"] = "insertPunch_req";           root["cmd"] = "insertPunch_OK";           //（punch表 - 插入记录）
root["cmd"] = "selectPunch_req";           root["cmd"] = "selectPunch_OK";           //（punch表 - 查找记录）
*/

// 表的格式
/*
CREATE TABLE timekeeper(userid text primary key, password text);       // 考勤员表【登录名、密码】
CREATE TABLE punchmachine(machineid text primary key, password text);  // 打卡机表【认证号、信息】
CREATE TABLE punch(employeeid text, time integer);                     // 打卡信息表【员工id、打卡时间】
CREATE TABLE employee(employeeid text primary key,                     // 员工信息表【员工id、员工姓名、......】
                      name text not null,
                      sex text,
                      phone integer,
                      time integer,
                      position text,
                      department text,
                      email text,
                      picture text);
*/

int handle_acceptfd(int acceptfd, sqlite3 *db);
int timekeeper_register(int acceptfd, sqlite3 *db, Json::Value root);
int timekeeper_login(int acceptfd, sqlite3 *db, Json::Value root);
int punchmachine_validate(int acceptfd, sqlite3 *db, Json::Value root);
int insert_employee(int acceptfd, sqlite3 *db, Json::Value root);
int select_employee(int acceptfd, sqlite3 *db, Json::Value root);
int update_employee(int acceptfd, sqlite3 *db, Json::Value root);
int delete_employee(int acceptfd, sqlite3 *db, Json::Value root);
int insert_punch(int acceptfd, sqlite3 *db, Json::Value root);
int select_punch(int acceptfd, sqlite3 *db, Json::Value root);

int main(int argc, char *argv[])
{
	sqlite3 *db;
	int socketfd;
	int acceptfd;
	struct sockaddr_in serveraddr;
	struct sockaddr_in clientaddr;
	socklen_t clientaddrlen = sizeof(clientaddr);
	pid_t pid;
	
	/*
	if(argc != 3)
	{
		printf("Usage:%s serverip serverport\n", argv[0]);
		return -1;
	}
	*/
	bzero(&serveraddr, sizeof(serveraddr));
	serveraddr.sin_family = AF_INET;
	serveraddr.sin_addr.s_addr = inet_addr("127.0.0.1");
	serveraddr.sin_port = htons(atoi("8888"));

	if(sqlite3_open("attendancesystem2.db", &db) != SQLITE_OK)
	{
		sqlite3_errmsg(db);
		sqlite3_close(db);
		return -1;
	}
	printf("sqlite3_open() success!\n");

	char *errmsg;
	char sqlcmd[256] = {};
	sprintf(sqlcmd, "CREATE TABLE IF NOT EXISTS timekeeper(userid text primary key, password text);");
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec() fail! %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	printf("sqlite3_exec(create table timekeeper) success!\n");

	sprintf(sqlcmd, "CREATE TABLE IF NOT EXISTS punchmachine(machineid text primary key, password text);");
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec() fail! %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	printf("sqlite3_exec(create table punchmachine) success!\n");
                                
	sprintf(sqlcmd, "CREATE TABLE IF NOT EXISTS employee(employeeid text primary key, name text not null, sex text, phone integer, time integer, position text, department text, email text);");
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec() fail! %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	printf("sqlite3_exec(create table employee) success!\n");

	sprintf(sqlcmd, "CREATE TABLE IF NOT EXISTS punch(employeeid text, time integer);");
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec() fail! %s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	printf("sqlite3_exec(create table punch) success!\n");

	if((socketfd = socket(AF_INET, SOCK_STREAM, 0)) < 0)
	{
		perror("socket() fail!");
		return -1;
	}
	printf("socket() success! [socketfd:%d]\n", socketfd);

	if(bind(socketfd, (struct sockaddr *)&serveraddr, sizeof(serveraddr)) < 0)
	{
		perror("bind() fail!");
		return -1;
	}
	printf("bind() success!\n");

	if(listen(socketfd, 10) < 0)
	{
		perror("listen() fail!");
		return -1;
	}
	printf("listen() success!\n");

	signal(SIGCHLD, SIG_IGN);
	while(1)
	{
		if((acceptfd = accept(socketfd, (struct sockaddr *)&clientaddr, &clientaddrlen)) < 0)
		{
			perror("accept() fail!");
			return -1;
		}
		printf("accept() success! [acceptfd:%d] From[%s:%d]\n", acceptfd, inet_ntoa(clientaddr.sin_addr), ntohs(clientaddr.sin_port));

		if((pid = fork()) < 0)
		{
			perror("fork() fail!");
			return -1;
		}
		else if(pid == 0)
		{
			close(socketfd);
			handle_acceptfd(acceptfd, db);
		}
		else
		{
			close(acceptfd);
		}
	}

	close(socketfd);
	sqlite3_close(db);
	return 0;
}

int handle_acceptfd(int acceptfd, sqlite3 *db)
{
	char buf[65536];
	Json::Value root;
	Json::Reader reader;
	while(recv(acceptfd, buf, 65536, 0) > 0)
	{
		reader.parse(buf, root);
		if(strcmp(root["cmd"].asString().c_str(), "timekeeperRegister_req") == 0)
		{
			printf("timekeeperRegister_req\n");
			timekeeper_register(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "timekeeperLogin_req") == 0)
		{
			printf("timekeeperLogin_req\n");
			timekeeper_login(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "punchmachineValidate_req") == 0)
		{
			printf("punchmachineValidate_req\n");
			punchmachine_validate(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "insertEmployee_req") == 0)
		{
			printf("insertEmployee_req\n");
			insert_employee(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "selectEmployee_req") == 0)
		{
			printf("selectEmployee_req\n");
			select_employee(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "updateEmployee_req") == 0)
		{
			printf("updateEmployee_req\n");
			update_employee(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "deleteEmployee_req") == 0)
		{
			printf("deleteEmployee_req\n");
			delete_employee(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "insertPunch_req") == 0)
		{
			printf("insertPunch_req\n");
			insert_punch(acceptfd, db, root);
		}
		else if(strcmp(root["cmd"].asString().c_str(), "selectPunch_req") == 0)
		{
			printf("selectPunch_req\n");
			select_punch(acceptfd, db, root);
		}
		else
		{
			printf("unknown cmd: %s\n", root["cmd"].asString().c_str());
		}
	}

	printf("Client exit! close(acceptfd:%d)\n", acceptfd);
	close(acceptfd);
	exit(0);

	return 0;
}

int timekeeper_register(int acceptfd, sqlite3 *db, Json::Value root)
{
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "insert into timekeeper values(\"%s\", \"%s\");", root["userid"].asString().c_str(), root["password"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec(insert timekeeper) fail!\n");
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}
	else
	{
		printf("sqlite3_exec(insert timekeeper) success!\n");
		root["cmd"] = "timekeeperRegister_OK";
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return 0;
	}
}

int timekeeper_login(int acceptfd, sqlite3 *db, Json::Value root)
{
	char **result;
	int nrow;
	int ncol;
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "select * from timekeeper where userid = \"%s\" and password = \"%s\";", root["userid"].asString().c_str(), root["password"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_get_table(db, sqlcmd, &result, &nrow, &ncol, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_get_table(select timekeeper) fail! %s\n", errmsg);
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return -1;
	}
	printf("sqlite3_get_table(select timekeeper) success!\n");

	if(nrow == 0)
	{
		printf("timekeeperLogin_FAIL\n");
		root["cmd"] = "timekeeperLogin_FAIL";
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return -1;
	}

	if(nrow == 1)
	{
		printf("timekeeperLogin_OK\n");
		root["cmd"] = "timekeeperLogin_OK";
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return 0;
	}

	return -1;
}

int punchmachine_validate(int acceptfd, sqlite3 *db, Json::Value root)
{
	char **result;
	int nrow;
	int ncol;
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "select * from punchmachine where machineid = \"%s\" and password = \"%s\";", root["machineid"].asString().c_str(), root["password"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_get_table(db, sqlcmd, &result, &nrow, &ncol, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_get_table(select punchmachine) fail! %s\n", errmsg);
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return -1;
	}
	printf("sqlite3_get_table(select punchmachine) success!\n");

	if(nrow == 0)
	{
		printf("punchmachineValidate_FAIL\n");
		root["cmd"] = "punchmachineValidate_FAIL";
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return -1;
	}

	if(nrow == 1)
	{
		printf("punchmachineValidate_OK\n");
		root["cmd"] = "punchmachineValidate_OK";
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return 0;
	}

	return -1;
}

int insert_employee(int acceptfd, sqlite3 *db, Json::Value root)
{	
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "insert into employee values(\"%s\", \"%s\", \"%s\", %s, %s, \"%s\", \"%s\", \"%s\");", root["employeeid"].asString().c_str(), root["name"].asString().c_str(), root["sex"].asString().c_str(), root["phone"].asString().c_str(), root["time"].asString().c_str(), root["position"].asString().c_str(), root["department"].asString().c_str(), root["email"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec(insert employee) fail!\n");
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}
	else
	{		
		printf("sqlite3_exec(insert employee) success!\n");
		root["cmd"] = "insertEmployee_OK";
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return 0;
	}
}

int select_employee(int acceptfd, sqlite3 *db, Json::Value root)
{
	char **result;
	int nrow;
	int ncol;
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "select * from employee where employeeid = \"%s\";", root["employeeid"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_get_table(db, sqlcmd, &result, &nrow, &ncol, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_get_table(select employee) fail! %s\n", errmsg);
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return -1;
	}
	printf("sqlite3_get_table(select employee) success!\n");

	if(nrow == 0)
	{
		printf("selectEmployee_FAIL\n");
		root["cmd"] = "selectEmployee_FAIL";
		send(acceptfd, writer.write(root).c_str(), 65536, 0);
		return -1;
	}

	printf("selectEmployee_OK\n");
	root["cmd"] = "selectEmployee_OK";
	root["select_result"]["nrow"] = nrow ;
	root["select_result"]["ncol"] = ncol ;
	Json::Value nresult;
	for(int i = 1; i <= nrow; i++)
	{
		for(int j = 0; j < ncol; j++)
		{
			nresult[result[j]] = result[i * ncol + j];
		}
		root["select_result"]["nresult"].append(nresult) ;
	}
	std::cout << root << std::endl;
	if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
	{
		perror("send() fail!");
	}
	return 0;
}

int update_employee(int acceptfd, sqlite3 *db, Json::Value root)
{
	char *errmsg;
	char sqlcmd[256] = "update employee";
	if(strcmp(root["name"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set name = \"%s\"", sqlcmd, root["name"].asString().c_str());
	if(strcmp(root["sex"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set sex = \"%s\"", sqlcmd, root["sex"].asString().c_str());
	if(strcmp(root["phone"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set phone = %s", sqlcmd, root["phone"].asString().c_str());
	if(strcmp(root["time"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set time = %s", sqlcmd, root["time"].asString().c_str());
	if(strcmp(root["position"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set position = \"%s\"", sqlcmd, root["position"].asString().c_str());
	if(strcmp(root["department"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set department = \"%s\"", sqlcmd, root["department"].asString().c_str());
	if(strcmp(root["email"].asString().c_str(), ""))
		sprintf(sqlcmd, "%s set email = \"%s\"", sqlcmd, root["email"].asString().c_str());
	sprintf(sqlcmd, "%s where employeeid = \"%s\";", sqlcmd, root["employeeid"].asString().c_str());
	printf("%s\n", sqlcmd);

	root.clear();
	Json::FastWriter writer;
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec(update employee) fail!\n");
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}
	else
	{
		printf("sqlite3_exec(update employee) success!\n");
		root["cmd"] = "updateEmployee_OK";
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return 0;
	}
}

int delete_employee(int acceptfd, sqlite3 *db, Json::Value root)
{
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "delete from employee where employeeid = \"%s\";", root["employeeid"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec(delete employee) fail!\n");
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}
	else
	{
		printf("sqlite3_exec(delete employee) success!\n");
		root["cmd"] = "deleteEmployee_OK";
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return 0;
	}
}

int insert_punch(int acceptfd, sqlite3 *db, Json::Value root)
{
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "insert into punch values(\"%s\", %s);", root["employeeid"].asString().c_str(), root["time"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_exec(insert punch) fail!\n");
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}
	else
	{
		printf("sqlite3_exec(insert punch) success!\n");
		root["cmd"] = "insertPunch_OK";
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return 0;
	}
}

int select_punch(int acceptfd, sqlite3 *db, Json::Value root)
{
	char **result;
	int nrow;
	int ncol;
	char *errmsg;
	char sqlcmd[128];
	sprintf(sqlcmd, "select * from punch where employeeid = \"%s\" and time >= %s and time < %s;", root["employeeid"].asString().c_str(), root["timestart"].asString().c_str(), root["timestop"].asString().c_str());
	printf("%s\n", sqlcmd);
	root.clear();
	Json::FastWriter writer;
	if(sqlite3_get_table(db, sqlcmd, &result, &nrow, &ncol, &errmsg) != SQLITE_OK)
	{
		printf("sqlite3_get_table(select punch) fail! %s\n", errmsg);
		root["cmd"] = errmsg;
		sqlite3_free(errmsg);
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}
	printf("sqlite3_get_table(select punch) success!\n");

	if(nrow == 0)
	{
		printf("selectPunch_FAIL\n");
		root["cmd"] = "selectPunch_FAIL";
		if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
		{
			perror("send() fail!");
		}
		return -1;
	}

	printf("selectPunch_OK\n");
	root["cmd"] = "selectPunch_OK";
	root["selcet_result"]["nrow"] = nrow ;
	root["selcet_result"]["ncol"] = ncol ;
	Json::Value nresult;
	for(int i = 1; i <= nrow; i++)
	{
		for(int j = 0; j < ncol; j++)
		{
			nresult[result[j]] = result[i * ncol + j];
		}
		root["selcet_result"]["nresult"].append(nresult) ;
	}
	std::cout << root << std::endl;
	if(send(acceptfd, writer.write(root).c_str(), 65536, 0) < 0)
	{
		perror("send() fail!");
	}
	return 0;
}
